var express = require('express');
var router = express.Router();
let sqlQuery=require('../lcSql')

/* 获得首页 */
router.get('/',async function(req, res, next) {
  let options=await getBooks(1,0,"");
  options.cid=0;
  options.searchInfo=0;
  res.render('index', options);
});

//分页  http://localhost:3000/category/0/page/2
router.get('/category/0/page/:pid',async (req,res)=>{
  let page=parseInt (req.params.pid)  
  let options=await getBooks(page,0,"")
  options.cid=0;
  options.searchInfo=0;
  res.render('index',options)
})
//种类分页
router.get('/category/:cid/page/:pid',async (req,res)=>{
  let page=parseInt(req.params.pid)
  let cid=parseInt(req.params.cid)
  
  let options=await getBooks(page,cid,"")
  options.cid=cid;
  options.searchInfo=0;
  res.render('index',options)
})

//搜索分页  http://localhost:3000/category/0/search/日本/page/2
router.get('/category/0/search/:val/page/:pid',async (req,res)=>{
  let cid=0;
  let page=parseInt(req.params.pid);
  let searchInfo=req.params.val;
  console.log(page,searchInfo);
  
  let options=await getBooks(page,cid,searchInfo);
  options.cid=cid;
  options.searchInfo=searchInfo;
  res.render('index',options)
})

//获得页面所需要的分页信息
async function getBooks(page,cid,searchInfo){
  var sqlStr,arr,sqlStr2,arr2;

  if(cid===0 && searchInfo==""){//不含种类，搜索的查询
    sqlStr='select id,bookname,bookimg,author,cataory from book limit ?,28';
    arr=[(page-1)*28];
    
    sqlStr2='select count(id) as num from book';
    arr2=[]
  }
  if(cid>0){//种类分页
    sqlStr='select id,bookname,bookimg,author,cataory from book WHERE cataory in (SELECT category from category WHERE id = ?) limit ?,28';
    arr=[cid,(page-1)*28];
    console.log(cid)
    sqlStr2= "select count(id) as num  from book WHERE cataory in (SELECT category from category WHERE id = ?)";
    arr2=[cid];
  }
  if(searchInfo){//搜索分页
    sqlStr="select id,bookname,bookimg,author,cataory from book where bookname like '%"+searchInfo+"%' or author like '%"+searchInfo+"%' limit ?,28";
    console.log(sqlStr);
    
    arr=[(page-1)*28];
    sqlStr2="select count(id) as num  from book where bookname like '%"+searchInfo+"%' or author like '%"+searchInfo+"%'";
    console.log(sqlStr2);
    
    arr2=[searchInfo]
  }
  let result=await sqlQuery(sqlStr,arr)
  
  var result2;
  if(arr2.length>0){  
     result2=await sqlQuery(sqlStr2,arr2)
  }else{
    result2=await sqlQuery(sqlStr2)
  }
  
  let allPage=Math.ceil(result2[0].num/28)
  
  //获得起始页
  let startPage = (page - 4)<1?1:(page-4);
  let endPage = (page+5)>allPage?allPage:page+5;
  
  let options={
    page:page,
    books:Array.from(result),
    cataorys:await(getCategory()),
    pageAll:allPage,
    startPage:startPage,
    endPage:endPage
  }
  return options
}
//获得种类
async function  getCategory(){
  let sqlStr="select * from category"
  return Array.from(await sqlQuery(sqlStr))
}

module.exports = router;
